from core.db import login
from utils.log import logger
from domain import Proxy
from utils.formats import dict_to_equal_str
import random
from settings import MAX_PROXIES_COUNT


class DbeaverPool(object):
    def __init__(self):
        self.conn = login.get_db_connection()
        self.cursor = self.conn.cursor()
        self.conn.select_db('proxies_pool')

    def is_exist(self, ip):
        """判断该ip是否在数据库中存在"""
        sql = f"select exists(select * from proxies where ip = '{ip}')"
        self.cursor.execute(sql)
        fetchall = self.cursor.fetchall()
        if fetchall[0][0] == 0:
            return False, Proxy(0, 0)
        else:
            sql = f"select * from proxies where ip = '{ip}'"
            self.cursor.execute(sql)
            proxy = Proxy(*self.cursor.fetchall()[0])
            return True, proxy

    def insert_one(self, proxy):
        """插入一个ip"""
        exist, temp = self.is_exist(proxy.ip)
        if not exist:
            # 获得values的字符串
            values = proxy.get_values_str()
            sql = f'insert into proxies values({values})'
            self.cursor.execute(sql)
            logger.info(f'插入ip:{proxy}')
        else:
            logger.info(f'已存在的ip:{proxy}')

    def update_one(self, proxy):
        """更新一个ip的信息"""
        exist, temp = self.is_exist(proxy.ip)
        if exist:
            equal_str = proxy.get_equal_str()
            sql = f"update proxies set {equal_str} where ip = '{proxy.ip}'"
            self.cursor.execute(sql)
            logger.info(f'更新ip:{proxy}')
        else:
            logger.warning(f'不存在的ip:{proxy}')

    def delete_one(self, proxy):
        """删除一个ip"""
        exist, temp = self.is_exist(proxy.ip)
        if exist:
            sql = f"delete from proxies where ip = '{proxy.ip}'"
            self.cursor.execute(sql)
            logger.info(f'删除ip:{proxy}')
        else:
            logger.warning(f'不存在的ip:{proxy}')

    def all_proxies(self):
        """查找所有ip, 用生成器返回ip对象"""
        sql = 'select * from proxies'
        self.cursor.execute(sql)
        proxies = self.cursor.fetchall()
        for tuple_proxy in proxies:
            proxy = Proxy(*tuple_proxy)
            yield proxy

    # 以下为提供代理api模块使用的功能
    def find(self, conditions: dict, count=0):
        """
        查询功能, 根据所给条件筛选代理ip, 返回指定数量大小的代理ip列表
        :param conditions:筛选条件字典
        :param count:查询数量最大值 不指定则为获取全部
        :return:满足要求的Proxy列表
        """
        attrs = dict_to_equal_str(conditions, 'and')
        if count:
            sql = f'select * from proxies where {attrs} order by score desc, speed asc limit {count}'
        else:
            sql = f'select * from proxies where {attrs} order by score desc, speed asc'
        self.cursor.execute(sql)
        proxies = self.cursor.fetchall()
        proxy_list = []
        for tuple_proxy in proxies:
            proxy = Proxy(*tuple_proxy)
            proxy_list.append(proxy)
        return proxy_list

    def get_proxies(self, protocol=None, domain=None, nick_type=0, count=MAX_PROXIES_COUNT):
        """
        根据协议类型和要访问的网站域名获取代理ip列表
        :param protocol: 协议 http, https
        :param domain: 域名 如jd.com
        :param nick_type: 匿名类型, 默认获取高匿代理ip
        :param count: 获取代理ip数量
        :return: 满足条件的代理ip
        """
        proxy_list = []
        conditions = {'nick_type': nick_type, 'protocol': 2}
        proxy_list.extend(self.find(conditions))  # http和https都满足的先全部选择
        if protocol.lower() == 'http':
            conditions['protocol'] = 0
        elif protocol.lower() == 'https':
            conditions['protocol'] = 1
        else:
            logger.warning("错误的参数输入: protocol")
            return proxy_list[:count]
        proxy_list.extend(self.find(conditions))
        proxy_list = [proxy for proxy in proxy_list if proxy.disable_domains.find(domain) == -1]
        return proxy_list[:count]

    def random_proxy(self, protocol=None, domain=None, nick_type=0):
        """
        根据协议类型和要访问的网站域名随机获取一个代理ip
        :param protocol: 协议 http, https
        :param domain: 域名 如jd.com
        :param nick_type: 匿名类型, 默认获取高匿代理ip
        :return: 满足条件的代理ip
        """
        proxy_list = self.get_proxies(protocol=protocol, domain=domain, nick_type=nick_type)
        try:
            proxy = random.choice(proxy_list)
            return proxy
        except IndexError as ie:
            logger.exception(ie)
            return Proxy(0, 0)

    def add_disable_domain(self, ip, disable_domain):
        """
        给代理ip添加不可用域名
        :param ip: 代理ip地址
        :param disable_domain: 不可用域名
        :return: bool-是否添加成功
        """
        exist, proxy = self.is_exist(ip)
        if exist:
            if proxy.disable_domains.find(disable_domain) == -1:
                proxy.disable_domains = proxy.disable_domains + disable_domain + ';'
                self.update_one(proxy)
                logger.info(f'为ip: {ip}添加不可用域名: {disable_domain}')
                return True
            else:
                logger.info(f'ip: {ip}已有不可用域名: {disable_domain}, 不重复添加')
        else:
            logger.warning(f'不存在的ip:{ip}')
        return False

    def __del__(self):
        self.cursor.close()
        self.conn.close()


if __name__ == '__main__':
    db_pool = DbeaverPool()
    p1 = Proxy('101.1.1.33', 88, 0, 0, 10.30, 'CN', 23, '')
    # p2 = Proxy('10102', 19, 1, 0, 12.92, 'CN', 42, '')
    # p3 = Proxy('10103', 14, 0, 0, 3.41, 'CN', 47, '')
    # p4 = Proxy('10104', 1113, 2, 0, 21.44, 'CN', 40, 'taobao.com;baidu.com;')
    db_pool.delete_one(p1)
    # db_pool.insert_one(p2)
    # db_pool.insert_one(p3)
    # db_pool.insert_one(p4)
    # db_pool.is_exist(p1.ip)
